﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;

namespace DataSet_To_MySQL
{
    public class DataAccess
    {
        public static MySqlConnection mySqlConnection;
        public static String connectionString;
        public static DataSet dataSetBuffer, dataSetResult;
        public static QueryStringBuilder queryStringBuilder;
        public static MySqlCommand mySQLCommand;
        public static MySqlDataAdapter mySQLDataAdapter;
        //public static Exception ex;

        #region 连接到数据库
        /// <summary>
        /// 建立连接命令并打开到数据库连接
        /// </summary>
        public void ConnectionBuild()
        {
            mySqlConnection = new MySqlConnection(connectionString);
            //mySqlConnection.ConnectionString = connectionStringBuilder.ToString();
            Console.WriteLine("Server Connecting ...");
            try
            {
                mySqlConnection.Open();
                Console.WriteLine("Server Connected Successfully！");
                Console.WriteLine("Connection State: "+mySqlConnection.State.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine("Server Connection Failed! \nDescription： " + ex.ToString());
            }
        }
        #endregion

        #region 查找操作
        /// <summary>
        /// 查找
        /// </summary>
        /// <param name="resultDataSet"></param>
        /// <param name="selectQueryString"></param>
        public DataSet SelectQuery(String selectQueryString)
        {
            try
            {
                mySQLDataAdapter = new MySqlDataAdapter(selectQueryString, mySqlConnection);
                dataSetResult = new DataSet();
                mySQLDataAdapter.Fill(dataSetResult);
                Console.WriteLine("Selected Data Successfully!");
                return dataSetResult;                
            }
            catch(Exception ex)
            {
                Console.WriteLine("Select Data Failed! \nDescription: " + ex.ToString());
                return null;
            }
        }
        #endregion

        #region 插入操作
        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="insertQueryString"></param>
        public void InsertQuery(String insertQueryString)
        {
            try
            {
                mySQLCommand = new MySqlCommand(insertQueryString, mySqlConnection);
                int i = mySQLCommand.ExecuteNonQuery();
                Console.WriteLine("Insert Successfully！\n");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Insert Error！\nDescription:" + ex.ToString() + "!");
            }


        }
        #endregion

        #region 更新操作
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="updateQueryString"></param>
        public void UpdateQuery(String updateQueryString)
        {
            try
            {
                mySQLCommand = new MySqlCommand(updateQueryString, mySqlConnection);
                int i = mySQLCommand.ExecuteNonQuery();
                Console.WriteLine("Update Successfully！\n");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Update Error！\nDescription:" + ex.ToString() + "!");
            } 
        }
        #endregion

        #region 删除操作
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="deleteQueryString"></param>
        public void DeleteQuery(String deleteQueryString)
        {
            try
            {
                mySQLCommand = new MySqlCommand(deleteQueryString, mySqlConnection);
                int i = mySQLCommand.ExecuteNonQuery();
                Console.WriteLine("Deleted Successfully！\n");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Delete Error！\nDescription:" + ex.ToString() + "!");
            } 
        }
        #endregion

        #region 关闭连接
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void ConnectionClose()
        {
            try
            {
                mySqlConnection.Close();
                Console.WriteLine("Server Disconnected Successfully！");
                Console.WriteLine("Connection State: " + mySqlConnection.State.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine("Server Disconnected Faild! \nDescription: " + ex.ToString());
            }
        }
        #endregion

        #region 析构函数
        /// <summary>
        /// 回收对象，关闭连接,释放对象资源
        /// </summary>
        ~DataAccess()
        {
            if (mySqlConnection.State.ToString() == "open")
            {
                try
                {
                    mySqlConnection.Close();
                    Console.WriteLine("Server Disconnected Successfully！");
                    Console.WriteLine("Connection State: " + mySqlConnection.State.ToString());
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Server Disconnected Faild! \nDescription: " + ex.ToString());
                }                
            }
            mySqlConnection.Dispose();
            Console.WriteLine("Server Dispose Successfully！");
            Console.WriteLine("Connection State: " + mySqlConnection.State.ToString());
        }
        #endregion
    }
}
